import re
from pathlib import Path
from typing import Literal, Optional

from openpyxl import load_workbook, Workbook
from openpyxl.cell import Cell, MergedCell
from openpyxl.styles import Border, Side, PatternFill, Alignment, Font
from openpyxl.worksheet.worksheet import Worksheet
from pydantic import BaseModel


class Offer(BaseModel):
    col: int
    bidder: str
    price: float
    starting_port: Literal["上海", "九江"]

    def __lt__(self, other):
        return self.price < other.price


class BidderFile(BaseModel):
    file_path: Path
    name: str
    bargaining: bool = False


MERGED_DATA_START_ROW = 3


class SeaTransportationMerger:
    def __init__(self, original_file: Path):
        self.original_file = original_file
        self.original_wb = load_workbook(original_file, data_only=True)
        self.original_sheet: Worksheet = self.original_wb.active
        self.merged_wb = Workbook()
        self.merged_sheet: Worksheet = self.merged_wb.active

    def merge(self, bidder_files: list[BidderFile], summary_file_path: Path):
        # 在处理原始表格前拆分合并单元格
        # unmerge_and_copy_cells(self.original_sheet)

        original_header_row = prepare_area_of_merged_data(
            self.original_sheet, self.merged_sheet
        )
        original_last_row = copy_products(
            self.original_sheet, self.merged_sheet, original_header_row
        )

        for bidder_file in bidder_files:
            bidder_wb = load_workbook(bidder_file.file_path, data_only=True)
            bidder_sheet: Worksheet = bidder_wb.active

            # 在处理投标文件前拆分合并单元格
            unmerge_and_copy_cells(bidder_sheet)

            merged_starting_col, bidder_last_row = merge_offers(
                bidder_sheet, self.merged_sheet, bidder_file.name
            )

            # if bidder_file.bargaining:

            #     # 如果是议价文件，则需要处理议价信息
            #     bargaining_file_path = bidder_file.file_path.with_suffix(
            #         ".bargaining.xlsx"
            #     )
            #     bargaining_wb = load_workbook(bargaining_file_path, data_only=True)
            #     bargaining_sheet = bargaining_wb.active
            #
            #     # 在处理议价文件前拆分合并单元格
            #     unmerge_and_copy_cells(bargaining_sheet)
            #
            #     if bargaining_sheet is None or not isinstance(
            #         bargaining_sheet, Worksheet
            #     ):
            #         raise ValueError("议价表格没有有效的工作表。")
            #     # 处理议价信息的逻辑
            #     merge_offer_on_bargaining(
            #         merged_sheet=self.merged_sheet,
            #         bargaining_sheet=bargaining_sheet,
            #         bidder_name=bidder_file.name,
            #     )

            # 复制额外信息
            copy_extra_info(
                bidder_sheet,
                self.merged_sheet,
                bidder_last_row + 1,
                self.merged_sheet.max_row + 3,
                bidder_file.name,
            )

        # 准备拟定中标区域
        proposed_winning_bid_col = prepare_area_of_proposed_winning_bid(
            self.merged_sheet
        )
        # 计算最低报价并填充拟定中标区域
        print("开始计算最低报价...", "-" * 10)
        obtain_minimum_offer(
            self.merged_sheet,
            proposed_winning_bid_col,
            original_last_row - original_header_row - 1,
        )
        # 保存合并后的文件
        self.merged_wb.save(summary_file_path)
        return summary_file_path


def unmerge_and_copy_cells(sheet: Worksheet):
    """
    拆分合并单元格并将内容复制到每个拆分后的单元格中。
    如果合并单元格的内容是“8类危险品”或者“普货”，则保留合并状态。
    """
    # 获取所有合并单元格的列表（需要转换为列表，因为 unmerge_cells 会修改原列表）
    merged_cells = list(sheet.merged_cells.ranges)

    for merged_range in merged_cells:
        # 获取合并区域的信息
        min_row, min_col, max_row, max_col = (
            merged_range.min_row,
            merged_range.min_col,
            merged_range.max_row,
            merged_range.max_col,
        )

        # 获取合并单元格的值
        top_left_cell = sheet.cell(row=min_row, column=min_col)
        cell_value = top_left_cell.value  # 明确命名为 cell_value，避免混淆

        # 如果合并单元格的内容是“8类危险品”或者“普货”，则跳过拆分
        if cell_value in ["8类危险品", "普货"]:
            continue

        # 保存样式属性
        font = top_left_cell.font.copy() if top_left_cell.font else None
        fill = top_left_cell.fill.copy() if top_left_cell.fill else None
        border = top_left_cell.border.copy() if top_left_cell.border else None
        alignment = top_left_cell.alignment.copy() if top_left_cell.alignment else None
        number_format = top_left_cell.number_format
        protection = (
            top_left_cell.protection.copy() if top_left_cell.protection else None
        )

        # 拆分合并单元格
        sheet.unmerge_cells(str(merged_range))

        # 将内容和样式复制到所有拆分后的单元格
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                cell = sheet.cell(row=row, column=col)
                cell.value = cell_value  # 只复制值

                # 分别应用样式属性
                if font:
                    cell.font = font
                if fill:
                    cell.fill = fill
                if border:
                    cell.border = border
                if alignment:
                    cell.alignment = alignment
                if number_format:
                    cell.number_format = number_format
                if protection:
                    cell.protection = protection


def copy_cell(source_cell: Cell, target_cell: Cell):
    """
    Copy the value and style from the original cell to the new cell.
    """
    if isinstance(source_cell, MergedCell) or isinstance(target_cell, MergedCell):
        return
    target_cell.value = source_cell.value
    if source_cell.has_style:
        if source_cell.font is not None:
            target_cell.font = source_cell.font.copy()
        if source_cell.fill is not None:
            target_cell.fill = source_cell.fill.copy()
        if source_cell.border is not None:
            target_cell.border = source_cell.border.copy()
        if source_cell.alignment is not None:
            target_cell.alignment = source_cell.alignment.copy()
        target_cell.number_format = source_cell.number_format
        if source_cell.protection is not None:
            target_cell.protection = source_cell.protection.copy()


def prepare_area_of_merged_data(original_sheet: Worksheet, new_sheet: Worksheet) -> int:
    """
    正式表表头在12行，12A.val为“目的港”。需要A-E以及K列。但是K列只有内容无表头。
    """
    header_row = 0
    for row in range(1, 15):
        if original_sheet.cell(row=row, column=1).value == "目的港":
            header_row = row
            break
    if header_row == 0:
        raise ValueError("未找到目的港所在的表头行。请检查原始表格格式。")
    # 设置第一行标题
    title_cell = new_sheet.cell(row=1, column=1)
    title_cell.value = "晶安高科海运评标"
    new_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)
    title_cell.font = Font(name="宋体", size=18, bold=True)
    title_cell.alignment = Alignment(horizontal="center", vertical="center")

    # 复制A-E列
    for col in range(1, 6):
        source_cell = original_sheet.cell(row=header_row, column=col)
        target_cell = new_sheet.cell(row=MERGED_DATA_START_ROW, column=col)
        if not isinstance(source_cell, MergedCell) and not isinstance(
            target_cell, MergedCell
        ):
            copy_cell(source_cell, target_cell)

    return header_row


def copy_products(
    original_sheet: Worksheet, new_sheet: Worksheet, original_header_row: int
) -> int:
    """
    从表头下一行开始。
    直到识别到A列为终止行内容：“请将人民币收费标准加入到报价中，招标现场会进行二次核对确认。未列明的收费项目将不能出现在以后的账单中！”。
    其中K列含合并单元格“8类危险品”和“普货”。分辨的标准是内容中的空行。K列无表头。
    :param original_sheet: Worksheet 原始表格
    :param new_sheet: Worksheet 新表格
    :param original_header_row: int 原表中的表头行
    :return: int 原表中的产品终止行
    """
    latest_product_row = 0
    # 查找产品列表的终止行
    for row in range(original_header_row, original_sheet.max_row + 1):
        val = original_sheet.cell(row=row, column=1).value
        if isinstance(val, str) and val.startswith("请将人民币收费标准加入到报价中"):
            latest_product_row = row
            break
    if latest_product_row == 0:
        raise ValueError("未找到产品列表的终止行。请检查原始表格格式。")

    current_row_in_new_sheet = MERGED_DATA_START_ROW + 1

    for row in range(original_header_row + 1, latest_product_row):
        source_port_value = original_sheet.cell(row=row, column=1).value

        # 检查目标表中当前行的目的港是否与源表一致
        target_port_value = new_sheet.cell(row=current_row_in_new_sheet, column=1).value

        if source_port_value != target_port_value:
            # 检查源表中是否确实存在此目的港
            if source_port_value is None or source_port_value == "":
                # 如果源表中目的港为空，跳过此行
                continue

            # 在目标表中插入一行
            new_sheet.insert_rows(current_row_in_new_sheet)
            new_sheet.cell(
                row=current_row_in_new_sheet, column=1
            ).value = source_port_value

        # 复制A-E列
        for col in range(1, 6):
            source_cell = original_sheet.cell(row=row, column=col)
            target_cell = new_sheet.cell(row=current_row_in_new_sheet, column=col)
            if not isinstance(source_cell, MergedCell) and not isinstance(
                target_cell, MergedCell
            ):
                copy_cell(source_cell, target_cell)

        current_row_in_new_sheet += 1

    # 处理K列的合并单元格
    merged_cells = original_sheet.merged_cells
    for merged_cell in merged_cells:
        if merged_cell.min_col == 11 and merged_cell.max_col == 11:
            # 复制合并单元格，但是位置需要调整
            min_row = merged_cell.min_row - original_header_row + MERGED_DATA_START_ROW
            max_row = merged_cell.max_row - original_header_row + MERGED_DATA_START_ROW
            min_col = 6
            max_col = 6
            new_sheet.merge_cells(
                start_row=min_row,
                start_column=min_col,
                end_row=max_row,
                end_column=max_col,
            )
            # 获取合并单元格的左上角单元格
            source_cell = original_sheet.cell(row=merged_cell.min_row, column=11)
            target_cell = new_sheet.cell(row=min_row, column=min_col)
            if not isinstance(source_cell, MergedCell) and not isinstance(
                target_cell, MergedCell
            ):
                copy_cell(source_cell, target_cell)

    # 添加“评标记录”四个字
    cell = new_sheet.cell(row=current_row_in_new_sheet, column=1)
    if not isinstance(cell, MergedCell):
        cell.value = "评标记录"

    return latest_product_row


def merge_offers(bidder_sheet: Worksheet, merged_sheet: Worksheet, bidder_name: str):
    """
    报价区域由F列“20尺小柜运价（美元每柜）”、G列“承运船公司”、H列“可提供舱位（柜）”、全部航程天数组成
    特殊情况：
    （1）一个目的港有多个承运船公司且运价不同，合并并使用‘、’分隔
    （2）J列备注（请确认可订舱位情况）在表中但是未在合并表中
    策略：
    （1）以K列和A列作为主键，在合并单元格中遵循取上方的值为主键。必须存在的主键为K列，否则跳过。
    """
    # 1 获取新表中的起始列并创建标题
    starting_col = 7
    tmp_val = merged_sheet.cell(row=MERGED_DATA_START_ROW, column=starting_col).value
    while tmp_val is not None:
        starting_col += 1
        tmp_val = merged_sheet.cell(
            row=MERGED_DATA_START_ROW, column=starting_col
        ).value

    headline_cell = merged_sheet.cell(
        row=MERGED_DATA_START_ROW - 1, column=starting_col
    )
    side = Side(style="thin", color="000000")
    headline_cell.border = Border(left=side, right=side, top=side, bottom=side)
    headline_cell.alignment = Alignment(horizontal="center", vertical="center")
    headline_cell.font = Font(bold=True)
    if not isinstance(headline_cell, MergedCell):
        headline_cell.value = bidder_name
    merged_sheet.merge_cells(
        start_row=MERGED_DATA_START_ROW - 1,
        start_column=starting_col,
        end_row=MERGED_DATA_START_ROW - 1,
        end_column=starting_col + 3,
    )
    # 2 查找招标表中的表头行
    header_row = 0
    for row in range(1, 15):
        if bidder_sheet.cell(row=row, column=1).value == "目的港":
            header_row = row
            break
    if header_row == 0:
        raise ValueError("未找到目的港所在的表头行。请检查原始表格格式。")
    # 2 复制表头
    for col in range(6, 10):
        source_cell = bidder_sheet.cell(row=header_row, column=col)
        target_cell = merged_sheet.cell(
            row=MERGED_DATA_START_ROW, column=starting_col + col - 6
        )
        copy_cell(source_cell, target_cell)
    # 3 查找产品列表的终止行
    latest_product_row = 0
    for row in range(header_row + 1, bidder_sheet.max_row + 1):
        val = bidder_sheet.cell(row=row, column=1).value
        if isinstance(val, str) and val.startswith("请将人民币收费标准加入到报价中"):
            latest_product_row = row
            break
    if latest_product_row == 0:
        raise ValueError("未找到产品列表的终止行。请检查原始表格格式。")

    # 4 合并同一目的港的多行内容
    group_rows = []
    current_group = None
    empty_rows = []  # 记录需要保留的空行

    for row in range(header_row + 1, latest_product_row):
        port_val = bidder_sheet.cell(row=row, column=1).value

        # 检查F~I列是否全为空
        is_fi_empty = all(
            bidder_sheet.cell(row=row, column=col).value in (None, "")
            for col in range(6, 10)
        )

        if not port_val and is_fi_empty:
            empty_rows.append(row)
            continue

        if port_val:
            # 新目的港
            if current_group:
                group_rows.append(current_group)
            current_group = {"port": port_val, "rows": [row]}
        else:
            # 继续归入当前目的港
            if current_group:
                current_group["rows"].append(row)

    if current_group:
        group_rows.append(current_group)

    merged_row_idx = MERGED_DATA_START_ROW + 1

    # 合并数据和空行，按原顺序插入
    all_rows = []
    for group in group_rows:
        all_rows.append(("group", group))
    for row in empty_rows:
        all_rows.append(("empty", row))

    # 按原表顺序排序
    all_rows.sort(key=lambda x: x[1]["rows"][0] if x[0] == "group" else x[1])

    for item in all_rows:
        if item[0] == "group":
            group = item[1]
            for col in range(6, 10):
                vals = []
                cell_objs = []
                for row in group["rows"]:
                    cell = bidder_sheet.cell(row=row, column=col)
                    cell_val = cell.value
                    if cell_val is not None and str(cell_val).strip() != "":
                        vals.append(cell_val)
                    cell_objs.append(cell)

                target_cell = merged_sheet.cell(
                    row=merged_row_idx,
                    column=starting_col + col - 6,
                )

                # 始终复制样式（用首行cell）
                if (
                    cell_objs
                    and not isinstance(cell_objs[0], MergedCell)
                    and not isinstance(target_cell, MergedCell)
                ):
                    copy_cell(cell_objs[0], target_cell)

                # 决定value - 多个值用"、"连接
                if vals:
                    if not isinstance(target_cell, MergedCell):
                        # 将所有非空值用"、"连接
                        target_cell.value = "、".join(str(v) for v in vals)
                else:
                    if not isinstance(target_cell, MergedCell):
                        target_cell.value = None

            merged_row_idx += 1

        elif item[0] == "empty":
            # 复制空行样式
            row = item[1]
            for col in range(6, 10):
                source_cell = bidder_sheet.cell(row=row, column=col)
                target_cell = merged_sheet.cell(
                    row=merged_row_idx, column=starting_col + col - 6
                )
                if not isinstance(source_cell, MergedCell) and not isinstance(
                    target_cell, MergedCell
                ):
                    copy_cell(source_cell, target_cell)
            merged_row_idx += 1

    return starting_col, latest_product_row


def get_price_from_cell(cell_value):
    """从单元格中提取价格列表，支持单个数值和用'、'分隔的多个数值"""
    if cell_value is None:
        return []

    if isinstance(cell_value, (int, float)):
        return [float(cell_value)]

    elif isinstance(cell_value, str):
        # 处理用"、"分隔的多个价格
        if "、" in cell_value:
            prices = []
            for price_str in cell_value.split("、"):
                price_str = price_str.strip()
                # 提取数字部分（可能包含小数点）
                try:
                    # 使用正则表达式提取数字（包括小数）
                    import re

                    number_match = re.search(r"(\d+\.?\d*)", price_str)
                    if number_match:
                        price = float(number_match.group(1))
                        prices.append(price)
                except (ValueError, AttributeError):
                    continue
            return prices
        else:
            # 单个价格字符串
            try:
                # 提取数字部分
                import re

                number_match = re.search(r"(\d+\.?\d*)", cell_value)
                if number_match:
                    return [float(number_match.group(1))]
            except (ValueError, AttributeError):
                return []

    return []


def get_lowest_offer(offers):
    """获取最低报价"""
    if offers:
        offers.sort()
        return offers[0]
    return None


def obtain_minimum_offer(
    merged_sheet: Worksheet, proposed_winning_bid_starting_col: int, product_num: int
):
    """
    获取最低报价。价格一般位于表头为“20尺小柜运价（美元每柜）”的列中。
    特殊情况：
    （1）价格cell中有多个价格，用“、”分隔的。取最低价格。
    （2）可能有议价的情况，位于“二次议价”列，该列一般插入于“承运船公司”后。
    注意：
    （1）分为“上海”和“九江”两种起始港口，单独计算各自的最低价。
    新增：最低价单元格填充黄色。
    """
    # 在函数开始处
    separator_row_index = -1
    for i in range(product_num):
        row_to_check = MERGED_DATA_START_ROW + i + 1
        # 假设通过检查A列是否为空来判断空行
        if merged_sheet.cell(row=row_to_check, column=1).value is None:
            separator_row_index = row_to_check
            break

    headline_row = MERGED_DATA_START_ROW - 1
    bidders = [
        {"name": merged_sheet.cell(row=headline_row, column=col).value, "col": col}
        for col in range(1, proposed_winning_bid_starting_col)
        if merged_sheet.cell(row=headline_row, column=col).value
    ]

    yellow_fill = PatternFill(fill_type="solid", fgColor="FFFF00")
    bold_font = Font(bold=True)
    for i in range(product_num):
        row = MERGED_DATA_START_ROW + i + 1

        if separator_row_index != -1:
            if row < separator_row_index:
                product_type = "8类危险品"
            elif row > separator_row_index:
                product_type = "普货"
            else:  # 当前行为分隔行本身，可以跳过
                continue
        offers = []

        for bidder in bidders:
            price_cell_value = merged_sheet.cell(row=row, column=bidder["col"]).value
            prices = get_price_from_cell(price_cell_value)
            for price in prices:
                offers.append(
                    Offer(
                        col=bidder["col"],
                        bidder=bidder["name"],
                        price=price,
                        starting_port=(
                            "上海" if bidder["name"].startswith("(上海起)") else "九江"
                        ),
                    )
                )

            # 检查并处理二次议价
            val = merged_sheet.cell(row=headline_row, column=bidder["col"] + 2).value
            if val and isinstance(val, str) and "二次议价" in val.lower():
                bargaining_price_cell_value = merged_sheet.cell(
                    row=row, column=bidder["col"] + 2
                ).value
                bargaining_prices = get_price_from_cell(bargaining_price_cell_value)
                for price in bargaining_prices:
                    offers.append(
                        Offer(
                            col=bidder["col"] + 2,
                            bidder=bidder["name"],
                            price=price,
                            starting_port=(
                                "上海"
                                if bidder["name"].startswith("(上海起)")
                                else "九江"
                            ),
                        )
                    )

        # 获取上海和九江的最低报价
        shanghai_offers = [offer for offer in offers if offer.starting_port == "上海"]
        jiujing_offers = [offer for offer in offers if offer.starting_port == "九江"]
        shanghai_min_offer = get_lowest_offer(shanghai_offers)
        jiujing_min_offer = get_lowest_offer(jiujing_offers)

        # 填充拟定中标区域
        # 1. 设置边框
        side = Side(style="thin", color="000000")
        for col in range(
            proposed_winning_bid_starting_col, proposed_winning_bid_starting_col + 5
        ):
            cell = merged_sheet.cell(row=row, column=col)
            cell.border = Border(left=side, right=side, top=side, bottom=side)
        # 2. 填入最低报价
        if shanghai_min_offer:
            cell1 = merged_sheet.cell(row=row, column=proposed_winning_bid_starting_col)
            cell2 = merged_sheet.cell(
                row=row, column=proposed_winning_bid_starting_col + 1
            )
            bidder_name_without_starting_port = re.sub(
                r"\(上海起\)", "", shanghai_min_offer.bidder
            )
            cell1.value = bidder_name_without_starting_port
            cell2.value = shanghai_min_offer.price
            cell1.font = bold_font
            cell2.font = bold_font

            # min to yellow fill
            merged_sheet.cell(row=row, column=shanghai_min_offer.col).fill = yellow_fill
        if jiujing_min_offer:
            cell3 = merged_sheet.cell(
                row=row, column=proposed_winning_bid_starting_col + 2
            )
            cell4 = merged_sheet.cell(
                row=row, column=proposed_winning_bid_starting_col + 3
            )
            bidder_name_without_starting_port = re.sub(
                r"\(九江起\)", "", jiujing_min_offer.bidder
            )
            cell3.value = bidder_name_without_starting_port
            cell4.value = jiujing_min_offer.price
            cell3.font = bold_font
            cell4.font = bold_font
            merged_sheet.cell(row=row, column=jiujing_min_offer.col).fill = yellow_fill

    return


def prepare_area_of_proposed_winning_bid(sheet: Worksheet) -> int:
    # 找到表头实际最后一列
    header_row = MERGED_DATA_START_ROW
    last_col = sheet.max_column
    while last_col > 0 and not sheet.cell(row=header_row, column=last_col).value:
        last_col -= 1
    starting_col = last_col + 1

    bold_font = Font(bold=True)
    border = Border(
        left=Side(style="thin", color="000000"),
        right=Side(style="thin", color="000000"),
        top=Side(style="thin", color="000000"),
        bottom=Side(style="thin", color="000000"),
    )
    headline_cell = sheet.cell(row=MERGED_DATA_START_ROW - 1, column=starting_col)
    if not isinstance(headline_cell, MergedCell):
        headline_cell.value = "拟定中标"
        headline_cell.font = bold_font
        headline_cell.border = border
    sheet.merge_cells(
        start_row=MERGED_DATA_START_ROW - 1,
        start_column=starting_col,
        end_row=MERGED_DATA_START_ROW - 1,
        end_column=starting_col + 4,
    )
    # headline部分加粗并加边框
    for col in range(starting_col, starting_col + 5):
        cell = sheet.cell(row=MERGED_DATA_START_ROW - 1, column=col)
        cell.font = bold_font
        cell.border = border
    header_list = ["供应商（上海）", "运价", "供应商（九江）", "运价", "备注"]
    for i, header in enumerate(header_list):
        cell = sheet.cell(row=MERGED_DATA_START_ROW, column=starting_col + i)
        if not isinstance(cell, MergedCell):
            cell.value = header
            cell.alignment = Alignment(
                horizontal="center", vertical="center", wrap_text=True
            )
            cell.font = bold_font
            cell.border = border
    # 设置拟定中标表头竖直和水平居中，自动换行
    for col in range(starting_col, starting_col + 5):
        cell = sheet.cell(row=MERGED_DATA_START_ROW, column=col)
        cell.alignment = Alignment(
            horizontal="center", vertical="center", wrap_text=True
        )
        cell.font = bold_font
        cell.border = border
    return starting_col


def _get_actual_last_row(sheet: Worksheet) -> int:
    """
    获取实际的最后一行。因为有可能存在空行。
    :param sheet: Worksheet
    :return: int
    """
    for row in range(sheet.max_row, 0, -1):
        if any(cell.value is not None for cell in sheet[row]):
            return row
    return 0


def copy_extra_info(
    source: Worksheet,
    target: Worksheet,
    source_starting_row: int,
    target_starting_row: int,
    bidder_name: Optional[str] = None,
):
    """
    在获取报价之后，复制额外信息到新表中。
    每个表都会有额外的信息，除了招标表。这些信息位于数据表之下。
    并非一定存在，但是也需要标注。
    新增：复制前插入一行标题，内容为投标方名，合并横向10个单元格并加黑色外边框。
    """

    source_last_row = _get_actual_last_row(source)
    # 插入标题行
    if bidder_name:
        title_row = target_starting_row
        title_cell = target.cell(row=title_row, column=1)
        black_side = Side(style="thin", color="000000")
        title_cell.border = Border(
            top=black_side, bottom=black_side, left=black_side, right=black_side
        )
        title_cell.alignment = Alignment(horizontal="center", vertical="center")
        title_cell.fill = PatternFill(start_color="ffff00", fill_type="solid")

        if not isinstance(title_cell, MergedCell):
            title_cell.value = bidder_name

        target.merge_cells(
            start_row=title_row, start_column=1, end_row=title_row, end_column=10
        )

    # 复制内容
    for row in range(source_starting_row, source_last_row + 1):
        for col in range(1, source.max_column + 1):
            source_cell = source.cell(row=row, column=col)
            target_cell = target.cell(
                row=row
                - source_starting_row
                + target_starting_row
                + (1 if bidder_name else 0),
                column=col,
            )
            if not isinstance(source_cell, MergedCell) and not isinstance(
                target_cell, MergedCell
            ):
                copy_cell(source_cell, target_cell)
    if source_starting_row == (source_last_row + 1):
        # 填个无
        cell = target.cell(row=target_starting_row + 1, column=1)
        if not isinstance(cell, MergedCell):
            cell.value = "无"
    return


def merge_offer_on_bargaining(
    merged_sheet: Worksheet,
    bidder_name: str,
    bargaining_sheet: Worksheet,
):
    """
    加入二次议价的报价。
    """
    # 1 查找合并表中的供应商
    bidder_offer_starting_row = 0
    for col in range(1, merged_sheet.max_column + 1):
        if (
            merged_sheet.cell(row=MERGED_DATA_START_ROW - 1, column=col).value
            == bidder_name
        ):
            bidder_offer_starting_row = col
            break
    if bidder_offer_starting_row == 0:
        raise ValueError(f"未找到供应商 {bidder_name} 的报价信息。请检查合并表格格式。")
    # 2 查找议价表中的表头行
    header_row = 0
    for row in range(1, 15):
        if bargaining_sheet.cell(row=row, column=1).value == "目的港":
            header_row = row
            break
    if header_row == 0:
        raise ValueError("未找到目的港所在的表头行。请检查议价表格格式。")
    # 3 仅使用“20尺小柜运价（美元每柜）”列，插入位置为合并表中承运船公司的下一列
    # 插入
    bargaining_col = bidder_offer_starting_row + 2
    merged_sheet.insert_cols(bargaining_col)
    price_col = 6
    # 复制表头
    source_cell = bargaining_sheet.cell(row=header_row, column=price_col)
    target_cell = merged_sheet.cell(row=MERGED_DATA_START_ROW, column=bargaining_col)
    if not isinstance(source_cell, MergedCell) and not isinstance(
        target_cell, MergedCell
    ):
        copy_cell(source_cell, target_cell)
        target_cell.value = f"二次议价"
    # 查找产品列表的终止行
    latest_product_row = 0
    for row in range(header_row + 1, bargaining_sheet.max_row + 1):
        val = bargaining_sheet.cell(row=row, column=1).value
        if isinstance(val, str) and val.startswith("请将人民币收费标准加入到报价中"):
            latest_product_row = row
            break
    if latest_product_row == 0:
        raise ValueError("未找到产品列表的终止行。请检查议价表格格式。")
    # 分组合并逻辑（与merge_offers一致）
    group_rows = []
    current_group = None
    empty_rows = []  # 记录需要保留的空行
    for row in range(header_row + 1, latest_product_row):
        port_val = bargaining_sheet.cell(row=row, column=1).value
        # 检查报价列是否为空
        is_price_empty = bargaining_sheet.cell(row=row, column=price_col).value in (
            None,
            "",
        )
        if not port_val and is_price_empty:
            empty_rows.append(row)
            continue
        if port_val:
            if current_group:
                group_rows.append(current_group)
            current_group = {"port": port_val, "rows": [row]}
        else:
            if current_group:
                current_group["rows"].append(row)
    if current_group:
        group_rows.append(current_group)

    merged_row_idx = MERGED_DATA_START_ROW + 1
    # 合并数据和空行，按原顺序插入
    all_rows = []
    for group in group_rows:
        all_rows.append(("group", group))
    for row in empty_rows:
        all_rows.append(("empty", row))
    all_rows.sort(key=lambda x: x[1]["rows"][0] if x[0] == "group" else x[1])

    for item in all_rows:
        if item[0] == "group":
            group = item[1]
            vals = []
            cell_objs = []
            for row in group["rows"]:
                cell = bargaining_sheet.cell(row=row, column=price_col)
                cell_val = cell.value
                if cell_val is not None and str(cell_val).strip() != "":
                    vals.append(cell_val)
                cell_objs.append(cell)
            target_cell = merged_sheet.cell(
                row=merged_row_idx,
                column=bargaining_col,
            )
            # 始终复制样式
            if (
                cell_objs
                and not isinstance(cell_objs[0], MergedCell)
                and not isinstance(target_cell, MergedCell)
            ):
                copy_cell(cell_objs[0], target_cell)
            # 决定value
            if len(vals) == 1:
                if not isinstance(target_cell, MergedCell):
                    target_cell.value = vals[0]
            elif len(vals) > 1:
                if not isinstance(target_cell, MergedCell):
                    target_cell.value = "、".join(str(v) for v in vals)
            else:
                if not isinstance(target_cell, MergedCell):
                    target_cell.value = None
            merged_row_idx += 1
        elif item[0] == "empty":
            row = item[1]
            source_cell = bargaining_sheet.cell(row=row, column=price_col)
            target_cell = merged_sheet.cell(row=merged_row_idx, column=bargaining_col)
            if not isinstance(source_cell, MergedCell) and not isinstance(
                target_cell, MergedCell
            ):
                copy_cell(source_cell, target_cell)
            merged_row_idx += 1
    return


if __name__ == "__main__":
    # Example usage
    merger = SeaTransportationMerger(Path("original.xlsx"))
    bidder_files = [
        BidderFile(file_path=Path("bidder1.xlsx"), name="Bidder 1（上海）"),
        BidderFile(
            file_path=Path("bidder2.xlsx"), name="Bidder 2（九江）", bargaining=True
        ),
    ]
    summary_file_path = Path("merged_summary.xlsx")
    merger.merge(bidder_files, summary_file_path)
    print(f"合并后的文件已保存到 {summary_file_path}")
